package com.o2o.cleaning.month.platform.ebusiness_plat.meituan

import com.alibaba.fastjson.JSON
import org.apache.spark.sql.{DataFrame, Dataset, Row, SparkSession}

object Relation_Address_meituan {

  /**
    * 地址标准映射表
    * 表数据字段说明：链接：http://192.168.0.74:8090/pages/viewpage.action?pageId=3932677  （处理组共享空间 -> 规范 -> 字段规范 -> 维度表说明 -> 地址表说明）
    *
    * @param spark
    * @return
    */
  def getAddressMapping(spark: SparkSession, AddressMapping: String): DataFrame = {

    val standardTableDF = spark.read.format("csv")
      .option("header", true).option("delimiter", ",").load(AddressMapping)
      .select(
        "administrative_region",
        "city",
        "city_grade",
        "city_origin",
        "district",
        "district_origin",
        "economic_division",
        "if_city",
        "if_district",
        "if_state_level_new_areas",
        "poor_counties",
        "province",
        "uniqueid", // 地级市 或 县对应的 (单月一对一关系的) 行政区域代码 --：省2位，市4位，县6位
        "uniqueid_nosplit", // 地级市 或 县对应的 (单月一对一关系的) 行政区域代码 --：6位
        "region_id", // 对应ES数据中的 regional_ID --： 省2位，市4位，县6位
        "region_id_nosplit", // 对应ES数据中的 regional_ID --:6位
        "rural_demonstration_counties",
        "rural_ecommerce",
        "the_belt_and_road_city",
        "the_belt_and_road_province",
        "the_yangtze_river_economic_zone_city",
        "the_yangtze_river_economic_zone_province",
        "urban_agglomerations"
      )
      .filter("uniqueid< 11 or uniqueid > 66") //过滤掉31个省级数据 （AddressMapping 里已过滤了）

    standardTableDF
  }


  /**
    * 更新旧的或特殊的 region_id 为地址表里的 region_id
    *
    * @param spark
    * @param dataFrame
    * @return
    */
  def updateOldRegionalID(spark: SparkSession, dataFrame: DataFrame): DataFrame = {

    dataFrame.createOrReplaceTempView("mongodbData_new_v")
    val mongodbData_new_region = spark.sql(
      """
        |select *,
        |case
        |when province rlike '香港' then '810000'
        |when province rlike '澳门' then '820000'
        |when province rlike '澳門' then '820000'
        |when province rlike '台湾' then '710000'
        |
        |when address rlike '深圳市光明新区' or address rlike '深圳市光明区'  then '440311'
        |
        |-- 绍兴市	柯桥区 330603
        |when regional_ID='330621' then '330603'
        |-- 淮安市	清江浦区
        |when regional_ID='320802' then '320812'
        |-- 松原市	扶余市
        |when regional_ID='220724' then '220781'
        |-- 山南市
        |when regional_ID='542200' then '540500'
        |--儋州市
        |when regional_ID='469003' then '460400'
        |--
        |when regional_ID='370117' then '371203' -- 莱芜市 钢城区  目前为 济南市 莱芜市 地址表暂时不做更新
        |when regional_ID='370116' then '371202'
        |when regional_ID='232718' then '232701'
        |when regional_ID='330204' then '330212' -- 浙江宁波市下面的江东区已经在2016年9月撤销，归鄞州区管辖,地址表暂时不做更新
        |
        |-- 四个直辖市
        |when regional_ID='110000' then '110100'
        |when regional_ID='120000' then '120100'
        |when regional_ID='310000' then '310100'
        |when regional_ID='500000' then '500100'
        |
        |else regional_ID end  newData_regional_ID
        |from mongodbData_new_v
      """.stripMargin).drop("regional_ID").withColumnRenamed("newData_regional_ID", "regional_ID")

    mongodbData_new_region

  }


  /**
    * 关联地址标签
    *
    * @param spark
    * @param dataFrame
    * @param addressMappingDF
    * @return
    */
  def withAddressMapping(spark: SparkSession, dataFrame: DataFrame, addressMappingDF: DataFrame): DataFrame = {

    // 用 region_id 或 region_id_nosplit 关联地址数据
    addressMappingDF.selectExpr(
      "administrative_region",
      "city",
      "city_grade",
      "city_origin",
      "district",
      "district_origin",
      "economic_division",
      "if_city",
      "if_district",
      "if_state_level_new_areas",
      "poor_counties",
      "province",
      "uniqueid",
      "uniqueid_nosplit",
      "region_id as region_id_match",
      "rural_demonstration_counties",
      "rural_ecommerce",
      "the_belt_and_road_city",
      "the_belt_and_road_province",
      "the_yangtze_river_economic_zone_city",
      "the_yangtze_river_economic_zone_province",
      "urban_agglomerations"
    )
      .union(addressMappingDF.filter("length(uniqueid)=4 or length(uniqueid)=2")
        .selectExpr(
          "administrative_region",
          "city",
          "city_grade",
          "city_origin",
          "district",
          "district_origin",
          "economic_division",
          "if_city",
          "if_district",
          "if_state_level_new_areas",
          "poor_counties",
          "province",
          "uniqueid",
          "uniqueid_nosplit",
          "region_id_nosplit as region_id_match",
          "rural_demonstration_counties",
          "rural_ecommerce",
          "the_belt_and_road_city",
          "the_belt_and_road_province",
          "the_yangtze_river_economic_zone_city",
          "the_yangtze_river_economic_zone_province",
          "urban_agglomerations"
        ))
      .createOrReplaceTempView("address_v")

    dataFrame.createOrReplaceTempView("data_v")

    spark.sql(
      """
        |select
        |*
        |from
        |(
        |   select a.*,case when region_id_match is null then '0' else region_id_match end resultID
        |   from
        |   data_v a
        |   left join
        |   address_v b
        |   on
        |   a.regional_ID = b.region_id_match
        |) c
        |left join
        |address_v d
        |on
        |c.resultID = d.region_id_match
      """.stripMargin)
      .drop("resultID", "regional_ID", "region_id_match")
      .dropDuplicates("shopId")

  }


  /**
    * NEED: "shopId", "city", "district", "district_origin", "address"
    * RETURN:  "town"
    *
    * 匹配广东省 中山市 及 东莞市 的下属的区域 --: 镇
    *
    * @param spark
    * @param platform
    * @param dataFrame
    * @return
    *
    */
  def match_ZSDG(spark: SparkSession, platform: String, dataFrame: DataFrame): DataFrame = {
    println("match_ZSDG333333333333")
    val rdd = dataFrame.toJSON.rdd.map(x => {

      val nObject = JSON.parseObject(x)

      val longitude = nObject.getOrDefault("longitude", "-1").toString().toDouble
      val latitude = nObject.getOrDefault("latitude", "-1").toString().toDouble
      val address = nObject.getOrDefault("address", "-1").toString()
      val city = nObject.getOrDefault("city", "-1").toString()

      var town = "-1"
      if (longitude != -1 && latitude != -1 && (city.equals("中山市") || city.equals("东莞市"))) {
//        town = ReverseGeocodingUtils.getTown(longitude, latitude)
      }

      nObject.put("address", address + "|" + town)

      nObject.toString

    })
    println("getTown4444444444444")

    spark.read.json(rdd).selectExpr("shopId", "city", "district", "district_origin", "address")
      .createOrReplaceTempView("addressTable")
    println("getTown55555555")

    val addressSQL =
      """
        |select
        |case when city = '中山市'
        |
        |then (
        |case
        |when address rlike '黄圃' then '黄圃镇'
        |when address rlike '格林格电器' then '黄圃镇'
        |when address rlike '南头镇' then '南头镇'
        |when address rlike '海誉五金制品厂' then '南头镇'
        |when address rlike '东凤' then '东凤镇'
        |when address rlike '阜沙' then '阜沙镇'
        |when address rlike '小榄' then '小榄镇'
        |when address rlike '第二人民法院' then '小榄镇'
        |when address rlike '东升镇' then '东升镇'
        |when address rlike '古镇' then '古镇镇'
        |when address rlike '横栏' then '横栏镇'
        |when address rlike '公诚物流' then '横栏镇'
        |when address rlike '三角镇' then '三角镇'
        |when address rlike '民众镇' then '民众镇'
        |when address rlike '南朗' then '南朗镇'
        |when address rlike '港口镇' then '港口镇'
        |when address rlike '大涌' then '大涌镇'
        |when address rlike '卓旗山休闲公园' then '大涌镇'
        |when address rlike '沙溪' then '沙溪镇'
        |when address rlike '溪镇半岛路' then '沙溪镇'
        |when address rlike '三乡' then '三乡镇'
        |when address rlike '金天地花园' then '三乡镇'
        |when address rlike '宗正机械制造有限公司' then '三乡镇'
        |when address rlike '板芙' then '板芙镇'
        |when address rlike '神湾' then '神湾镇'
        |when address rlike '坦洲' then '坦洲镇'
        |when address rlike '石岐' then '石岐街道'
        |when address rlike '中山市城区' then '石岐街道'
        |when address rlike '岐关西路' then '石岐街道'
        |when address rlike '东区街道' then '东区街道'
        |when address rlike '中级人民法院' then '东区街道'
        |when address rlike '西区街道' then '西区街道'
        |when address rlike '南区街道' then '南区街道'
        |when address rlike '五桂山' then '五桂山街道'
        |when address rlike '火炬' then '中山火炬高技术产业开发区'
        |when address rlike '民族' then '石岐街道'
        |when address rlike '迎阳' then '石岐街道'
        |when address rlike '博爱' then '石岐街道'
        |when address rlike '中山市第一人民法院' then '石岐街道'
        |when address rlike '东明' then '石岐街道'
        |when address rlike '湖滨' then '石岐街道'
        |when address rlike '民权' then '石岐街道'
        |when address rlike '凤鸣' then '石岐街道'
        |when address rlike '太平' then '石岐街道'
        |when address rlike '仙湖' then '石岐街道'
        |when address rlike '民生' then '石岐街道'
        |when address rlike '悦来南' then '石岐街道'
        |when address rlike '莲新' then '石岐街道'
        |when address rlike '桂园' then '石岐街道'
        |when address rlike '宏基' then '石岐街道'
        |when address rlike '莲员' then '石岐街道'
        |when address rlike '康华' then '石岐街道'
        |when address rlike '大信' then '石岐街道'
        |when address rlike '莲兴' then '石岐街道'
        |when address rlike '东港湾' then '石岐街道'
        |when address rlike '花苑' then '东区街道'
        |when address rlike '竹苑' then '东区街道'
        |when address rlike '桃苑' then '东区街道'
        |when address rlike '夏洋' then '东区街道'
        |when address rlike '长江三溪' then '东区街道'
        |when address rlike '东裕' then '东区街道'
        |when address rlike '起湾' then '东区街道'
        |when address rlike '桥岗' then '东区街道'
        |when address rlike '齐富湾' then '东区街道'
        |when address rlike '新鳌岭' then '东区街道'
        |when address rlike '竹苑新' then '东区街道'
        |when address rlike '松苑新' then '东区街道'
        |when address rlike '细柏山' then '东区街道'
        |when address rlike '土瓜岭' then '东区街道'
        |when address rlike '长江' then '东区街道'
        |when address rlike '三溪' then '东区街道'
        |when address rlike '库充' then '东区街道'
        |when address rlike '亨尾' then '东区街道'
        |when address rlike '起湾' then '东区街道'
        |when address rlike '沙岗' then '东区街道'
        |when address rlike '槎桥' then '东区街道'
        |when address rlike '新安' then '东区街道'
        |when address rlike '齐东' then '东区街道'
        |when address rlike '老富头' then '东区街道'
        |when address rlike '白沙湾' then '东区街道'
        |when address rlike '洋角口' then '东区街道'
        |when address rlike '长洲' then '西区街道'
        |when address rlike '后山' then '西区街道'
        |when address rlike '西苑' then '西区街道'
        |when address rlike '烟洲' then '西区街道'
        |when address rlike '彩虹' then '西区街道'
        |when address rlike '广丰' then '西区街道'
        |when address rlike '沙朗' then '西区街道'
        |when address rlike '隆平' then '西区街道'
        |when address rlike '隆昌' then '西区街道'
        |when address rlike '环城' then '南区街道'
        |when address rlike '城南' then '南区街道'
        |when address rlike '沙涌' then '南区街道'
        |when address rlike '树涌' then '南区街道'
        |when address rlike '北台' then '南区街道'
        |when address rlike '恒美' then '南区街道'
        |when address rlike '渡头' then '南区街道'
        |when address rlike '竹秀园' then '南区街道'
        |when address rlike '南区' then '南区街道'
        |when address rlike '福涌' then '南区街道'
        |when address rlike '马岭' then '南区街道'
        |when address rlike '五桂山' then '五桂山街道'
        |when address rlike '长命水' then '五桂山街道'
        |when address rlike '石鼓' then '五桂山街道'
        |when address rlike '龙塘' then '五桂山街道'
        |when address rlike '南桥' then '五桂山街道'
        |when address rlike '桂南' then '五桂山街道'
        |when address rlike '张家边' then '中山火炬高技术产业开发区'
        |when address rlike '博凯' then '中山火炬高技术产业开发区'
        |when address rlike '联富' then '中山火炬高技术产业开发区'
        |when address rlike '六和' then '中山火炬高技术产业开发区'
        |when address rlike '城东' then '中山火炬高技术产业开发区'
        |when address rlike '海滨' then '中山火炬高技术产业开发区'
        |when address rlike '中山港' then '中山火炬高技术产业开发区'
        |when address rlike '康乐大道' then '中山火炬高技术产业开发区'
        |else '-1' end)
        |
        |when city='东莞市' then (
        |             case
        |             when address rlike '石龙镇' then '石龙镇'
        |             when address rlike '第一人民法院' then '石龙镇'
        |             when address rlike '李佐文粤' then '石龙镇'
        |             when address rlike '石排' then '石排镇'
        |             when address rlike '茶山' then '茶山镇'
        |             when address rlike '企石' then '企石镇'
        |             when address rlike '桥头镇' then '桥头镇'
        |             when address rlike '东坑' then '东坑镇'
        |             when address rlike '横沥' then '横沥镇'
        |             when address rlike '业扬电子' then '横沥镇'
        |             when address rlike '常平' then '常平镇'
        |             when address rlike '虎门镇' then '虎门镇'
        |             when address rlike '虎门' then '虎门镇'
        |             when address rlike '长安镇' then '长安镇'
        |             when address rlike '第二人民法院' then '长安镇'
        |             when address rlike '伟威土' then '长安镇'
        |             when address rlike '长安' then '长安镇'
        |             when address rlike '沙田' then '沙田镇'
        |             when address rlike '厚街' then '厚街镇'
        |             when address rlike '寮步' then '寮步镇'
        |             when address rlike '大岭山' then '大岭山镇'
        |             when address rlike '大朗' then '大朗镇'
        |             when address rlike '黄江' then '黄江镇'
        |             when address rlike '樟木头' then '樟木头镇'
        |             when address rlike '普拉司仓储' then '樟木头镇'
        |             when address rlike '谢岗' then '谢岗镇'
        |             when address rlike '塘厦' then '塘厦镇'
        |             when address rlike '第三人民法院' then '塘厦镇'
        |             when address rlike '清溪' then '清溪镇'
        |             when address rlike '凤岗' then '凤岗镇'
        |             when address rlike '亿日工业园' then '凤岗镇'
        |             when address rlike '雅路智能家' then '凤岗镇'
        |             when address rlike '麻涌' then '麻涌镇'
        |             when address rlike '中堂' then '中堂镇'
        |             when address rlike '高埗' then '高埗镇'
        |             when address rlike '石碣' then '石碣镇'
        |             when address rlike '望牛墩' then '望牛墩镇'
        |             when address rlike '洪梅' then '洪梅镇'
        |             when address rlike '道滘' then '道滘镇'
        |             when address rlike '莞城' then '莞城区'
        |             when address rlike '旗峰路' then '莞城区'
        |             when address rlike '香港街' then '莞城区'
        |             when address rlike '莞城街道' then '莞城区'
        |             when address rlike '和阳路' then '莞城区'
        |             when address rlike '南城区' then '南城区'
        |             when address rlike '城市风景' then '南城区'
        |             when address rlike '南城街道' then '南城区'
        |             when address rlike '渝安汽车' then '南城区'
        |             when address rlike '东城区' then '东城区'
        |             when address rlike '东城区' then '东城区'
        |             when address rlike '东纵路208号' then '东城区'
        |             when address rlike '中级人民法院' then '东城区'
        |             when address rlike '东城' then '东城区'
        |             when address rlike '东成' then '东城区'
        |             when address rlike '公安局' then '东城区'
        |             when address rlike '东莞市人保大厦' then '东城区'
        |             when address rlike '东城街道' then '东城区'
        |             when address rlike '万江区' then '万江区'
        |             when address rlike '万江街道' then '万江区'
        |             when address rlike '健康路' then '茶山镇'
        |else '-1' end)
        |else '-1' end town,
        |*
        |from
        |addressTable
      """.stripMargin
    val frame1 = spark.sql(addressSQL)
    println("getTown66666666")

    val town_have_been_Match_1: Dataset[Row] = frame1.where("town !='-1'")

    //将"town ='-1'"的进行town 的匹配  然后与"town !='-1'"的合并
    frame1.filter("town ='-1'").drop("town")
      .registerTempTable("th1Table")
    val addressSQL_town =
      """
        |select
        |case when city='中山市' then (
        |case
        |when address rlike '新沙' then '黄圃镇'
        |when address rlike '石军' then '黄圃镇'
        |when address rlike '马安' then '黄圃镇'
        |when address rlike '横档' then '黄圃镇'
        |when address rlike '大岑' then '黄圃镇'
        |when address rlike '鳌山' then '黄圃镇'
        |when address rlike '团范' then '黄圃镇'
        |when address rlike '吴栏' then '黄圃镇'
        |when address rlike '新地' then '黄圃镇'
        |when address rlike '大雁' then '黄圃镇'
        |when address rlike '兆丰' then '黄圃镇'
        |when address rlike '镇一' then '黄圃镇'
        |when address rlike '新糖' then '黄圃镇'
        |when address rlike '文明' then '黄圃镇'
        |when address rlike '三社' then '黄圃镇'
        |when address rlike '永平' then '黄圃镇'
        |when address rlike '南头' then '南头镇'
        |when address rlike '南城' then '南头镇'
        |when address rlike '民安' then '南头镇'
        |when address rlike '将军' then '南头镇'
        |when address rlike '滘心' then '南头镇'
        |when address rlike '穗西' then '南头镇'
        |when address rlike '北帝' then '南头镇'
        |when address rlike '伯公' then '东凤镇'
        |when address rlike '民乐' then '东凤镇'
        |when address rlike '东兴' then '东凤镇'
        |when address rlike '小沥' then '东凤镇'
        |when address rlike '东海' then '东凤镇'
        |when address rlike '同安' then '东凤镇'
        |when address rlike '安乐' then '东凤镇'
        |when address rlike '和泰' then '东凤镇'
        |when address rlike '穗成' then '东凤镇'
        |when address rlike '东和平' then '东凤镇'
        |when address rlike '吉昌' then '东凤镇'
        |when address rlike '东罡步' then '东凤镇'
        |when address rlike '西罡步' then '东凤镇'
        |when address rlike '永益' then '东凤镇'
        |when address rlike '阜圩' then '阜沙镇'
        |when address rlike '牛角' then '阜沙镇'
        |when address rlike '上南' then '阜沙镇'
        |when address rlike '卫民' then '阜沙镇'
        |when address rlike '阜东' then '阜沙镇'
        |when address rlike '罗松' then '阜沙镇'
        |when address rlike '阜沙' then '阜沙镇'
        |when address rlike '大有' then '阜沙镇'
        |when address rlike '丰联' then '阜沙镇'
        |when address rlike '埠港东路' then '阜沙镇'
        |when address rlike '新市' then '小榄镇'
        |when address rlike '沙口' then '小榄镇'
        |when address rlike '永宁' then '小榄镇'
        |when address rlike '九训基' then '小榄镇'
        |when address rlike '东区' then '小榄镇'
        |when address rlike '西区' then '小榄镇'
        |when address rlike '北区' then '小榄镇'
        |when address rlike '竹源' then '小榄镇'
        |when address rlike '盛丰' then '小榄镇'
        |when address rlike '埒西一' then '小榄镇'
        |when address rlike '联丰' then '小榄镇'
        |when address rlike '绩东一' then '小榄镇'
        |when address rlike '绩东二' then '小榄镇'
        |when address rlike '绩西' then '小榄镇'
        |when address rlike '宝丰' then '小榄镇'
        |when address rlike '裕民' then '东升镇'
        |when address rlike '同乐' then '东升镇'
        |when address rlike '高沙' then '东升镇'
        |when address rlike '东升' then '东升镇'
        |when address rlike '兆龙' then '东升镇'
        |when address rlike '同茂' then '东升镇'
        |when address rlike '利生' then '东升镇'
        |when address rlike '东城' then '东升镇'
        |when address rlike '益隆' then '东升镇'
        |when address rlike '新胜' then '东升镇'
        |when address rlike '胜龙' then '东升镇'
        |when address rlike '太平' then '东升镇'
        |when address rlike '坦背' then '东升镇'
        |when address rlike '白鲤' then '东升镇'
        |when address rlike '海洲' then '古镇镇'
        |when address rlike '古一' then '古镇镇'
        |when address rlike '古二' then '古镇镇'
        |when address rlike '古三' then '古镇镇'
        |when address rlike '古四' then '古镇镇'
        |when address rlike '六坊' then '古镇镇'
        |when address rlike '七坊' then '古镇镇'
        |when address rlike '冈东' then '古镇镇'
        |when address rlike '冈南' then '古镇镇'
        |when address rlike '曹一' then '古镇镇'
        |when address rlike '曹二' then '古镇镇'
        |when address rlike '曹三' then '古镇镇'
        |when address rlike '古镇' then '古镇镇'
        |when address rlike '西冲' then '横栏镇'
        |when address rlike '新丰' then '横栏镇'
        |when address rlike '横东' then '横栏镇'
        |when address rlike '横西' then '横栏镇'
        |when address rlike '贴边' then '横栏镇'
        |when address rlike '宝裕' then '横栏镇'
        |when address rlike '新茂' then '横栏镇'
        |when address rlike '裕祥' then '横栏镇'
        |when address rlike '五沙' then '横栏镇'
        |when address rlike '六沙' then '横栏镇'
        |when address rlike '三沙' then '横栏镇'
        |when address rlike '中心' then '三角镇'
        |when address rlike '结民' then '三角镇'
        |when address rlike '蟠龙' then '三角镇'
        |when address rlike '东南' then '三角镇'
        |when address rlike '沙栏' then '三角镇'
        |when address rlike '光明' then '三角镇'
        |when address rlike '高平' then '三角镇'
        |when address rlike '三角' then '三角镇'
        |when address rlike '民众' then '民众镇'
        |when address rlike '浪网' then '民众镇'
        |when address rlike '多宝' then '民众镇'
        |when address rlike '新伦' then '民众镇'
        |when address rlike '民平' then '民众镇'
        |when address rlike '新平' then '民众镇'
        |when address rlike '沙仔' then '民众镇'
        |when address rlike '锦标' then '民众镇'
        |when address rlike '义仓' then '民众镇'
        |when address rlike '裕安' then '民众镇'
        |when address rlike '新建' then '民众镇'
        |when address rlike '接源' then '民众镇'
        |when address rlike '沿江' then '民众镇'
        |when address rlike '群安' then '民众镇'
        |when address rlike '上网' then '民众镇'
        |when address rlike '浪网' then '民众镇'
        |when address rlike '新平四' then '民众镇'
        |when address rlike '东胜' then '民众镇'
        |when address rlike '三墩' then '民众镇'
        |when address rlike '南朗' then '南朗镇'
        |when address rlike '横门' then '南朗镇'
        |when address rlike '关塘' then '南朗镇'
        |when address rlike '白企' then '南朗镇'
        |when address rlike '龙穴' then '南朗镇'
        |when address rlike '华照' then '南朗镇'
        |when address rlike '冲口' then '南朗镇'
        |when address rlike '濠涌' then '南朗镇'
        |when address rlike '大车' then '南朗镇'
        |when address rlike '左步' then '南朗镇'
        |when address rlike '榄边' then '南朗镇'
        |when address rlike '南朗' then '南朗镇'
        |when address rlike '泮沙' then '南朗镇'
        |when address rlike '翠亨' then '南朗镇'
        |when address rlike '崖口' then '南朗镇'
        |when address rlike '群乐' then '港口镇'
        |when address rlike '胜隆' then '港口镇'
        |when address rlike '群众' then '港口镇'
        |when address rlike '石特' then '港口镇'
        |when address rlike '西街' then '港口镇'
        |when address rlike '民主' then '港口镇'
        |when address rlike '港口' then '港口镇'
        |when address rlike '下南' then '港口镇'
        |when address rlike '中南' then '港口镇'
        |when address rlike '兴涌' then '大涌镇'
        |when address rlike '大涌' then '大涌镇'
        |when address rlike '南文' then '大涌镇'
        |when address rlike '安堂' then '大涌镇'
        |when address rlike '岚田' then '大涌镇'
        |when address rlike '青岗' then '大涌镇'
        |when address rlike '旗南' then '大涌镇'
        |when address rlike '旗北' then '大涌镇'
        |when address rlike '汇源' then '沙溪镇'
        |when address rlike '康乐' then '沙溪镇'
        |when address rlike '龙山' then '沙溪镇'
        |when address rlike '龙瑞' then '沙溪镇'
        |when address rlike '云汉' then '沙溪镇'
        |when address rlike '乐群' then '沙溪镇'
        |when address rlike '龙头环' then '沙溪镇'
        |when address rlike '圣狮' then '沙溪镇'
        |when address rlike '象角' then '沙溪镇'
        |when address rlike '沙溪' then '沙溪镇'
        |when address rlike '虎逊' then '沙溪镇'
        |when address rlike '濠涌' then '沙溪镇'
        |when address rlike '涌头' then '沙溪镇'
        |when address rlike '中兴' then '沙溪镇'
        |when address rlike '涌边' then '沙溪镇'
        |when address rlike '港园' then '沙溪镇'
        |when address rlike '圩仔' then '三乡镇'
        |when address rlike '前陇' then '三乡镇'
        |when address rlike '南龙' then '三乡镇'
        |when address rlike '白石' then '三乡镇'
        |when address rlike '平南' then '三乡镇'
        |when address rlike '平东' then '三乡镇'
        |when address rlike '桥头' then '三乡镇'
        |when address rlike '乌石' then '三乡镇'
        |when address rlike '大布' then '三乡镇'
        |when address rlike '雍陌' then '三乡镇'
        |when address rlike '西山' then '三乡镇'
        |when address rlike '古鹤' then '三乡镇'
        |when address rlike '新圩' then '三乡镇'
        |when address rlike '茅湾' then '三乡镇'
        |when address rlike '鸦岗' then '三乡镇'
        |when address rlike '泉眼' then '三乡镇'
        |when address rlike '塘敢' then '三乡镇'
        |when address rlike '板芙' then '板芙镇'
        |when address rlike '板芙' then '板芙镇'
        |when address rlike '板尾' then '板芙镇'
        |when address rlike '四联' then '板芙镇'
        |when address rlike '禄围' then '板芙镇'
        |when address rlike '广福' then '板芙镇'
        |when address rlike '金钟' then '板芙镇'
        |when address rlike '里溪' then '板芙镇'
        |when address rlike '深湾' then '板芙镇'
        |when address rlike '白溪' then '板芙镇'
        |when address rlike '湖洲' then '板芙镇'
        |when address rlike '神湾' then '神湾镇'
        |when address rlike '神溪' then '神湾镇'
        |when address rlike '宥南' then '神湾镇'
        |when address rlike '海港' then '神湾镇'
        |when address rlike '外沙' then '神湾镇'
        |when address rlike '竹排' then '神湾镇'
        |when address rlike '坦洲' then '坦洲镇'
        |when address rlike '合胜' then '坦洲镇'
        |when address rlike '安阜' then '坦洲镇'
        |when address rlike '同胜' then '坦洲镇'
        |when address rlike '十四' then '坦洲镇'
        |when address rlike '金斗' then '坦洲镇'
        |when address rlike '联一' then '坦洲镇'
        |when address rlike '群联' then '坦洲镇'
        |when address rlike '裕洲' then '坦洲镇'
        |when address rlike '新合' then '坦洲镇'
        |when address rlike '永一' then '坦洲镇'
        |when address rlike '永二' then '坦洲镇'
        |when address rlike '新前进' then '坦洲镇'
        |when address rlike '芙蓉' then '板芙镇'
        |when address rlike '深港市场' then '板芙镇'
        |when address rlike '詹园' then '板芙镇'
        |-- when address rlike '健康路' then '茶山镇'
        |when address rlike '西堡' then '大涌镇'
        |when address rlike '新地' then '大涌镇'
        |when address rlike '四埒涌路' then '东凤镇'
        |when address rlike '向阳' then '东凤镇'
        |when address rlike '小沥社区' then '东凤镇'
        |when address rlike '永安路' then '东凤镇'
        |when address rlike '永乐路' then '东凤镇'
        |when address rlike '民顺街' then '东区街道'
        |when address rlike '欧朗特' then '东区街道'
        |when address rlike '桃苑路' then '东区街道'
        |when address rlike '银湾东路' then '东区街道'
        |when address rlike '御景湾' then '东区街道'
        |when address rlike '中山三路' then '东区街道'
        |when address rlike '东升' then '东升镇'
        |when address rlike '岗南中心路' then '东升镇'
        |when address rlike '惠红路' then '东升镇'
        |when address rlike '同昌' then '东升镇'
        |when address rlike '迎宾路' then '东升镇'
        |when address rlike '福城东路' then '阜沙镇'
        |when address rlike '阜城' then '阜沙镇'
        |when address rlike '锦绣路' then '阜沙镇'
        |when address rlike '文安市场' then '阜沙镇'
        |when address rlike '广安' then '港口镇'
        |when address rlike '惠安市场' then '港口镇'
        |when address rlike '鸿福横路' then '古镇镇'
        |when address rlike '嘉年华' then '古镇镇'
        |when address rlike '龙庭街' then '古镇镇'
        |when address rlike '胜利广场' then '古镇镇'
        |when address rlike '万维广场' then '古镇镇'
        |when address rlike '温馨家园' then '古镇镇'
        |when address rlike '文明路' then '古镇镇'
        |when address rlike '西环' then '古镇镇'
        |when address rlike '新兴中路' then '古镇镇'
        |when address rlike '长塘大街' then '古镇镇'
        |when address rlike '顺兴南路' then '横栏镇'
        |when address rlike '五一' then '横栏镇'
        |when address rlike '西冲市场' then '横栏镇'
        |when address rlike '新丰综合市场' then '横栏镇'
        |when address rlike '永谊' then '横栏镇'
        |when address rlike '成业大道' then '黄圃镇'
        |when address rlike '商业大街' then '黄圃镇'
        |when address rlike '城南' then '南区街道'
        |when address rlike '红光西路' then '南区街道'
        |when address rlike '南街' then '南区街道'
        |when address rlike '南路' then '南区街道'
        |when address rlike '河尾街' then '南头镇'
        |when address rlike '聚盛街' then '南头镇'
        |when address rlike '同乐西路' then '南头镇'
        |when address rlike '高平大道' then '三角镇'
        |when address rlike '和谐路' then '三角镇'
        |when address rlike '聚贤北路' then '三角镇'
        |when address rlike '人民路' then '三角镇'
        |when address rlike '宝龙' then '三乡镇'
        |when address rlike '康乐花园' then '三乡镇'
        |when address rlike '丽景' then '三乡镇'
        |when address rlike '泉安路' then '三乡镇'
        |when address rlike '文昌东路' then '三乡镇'
        |when address rlike '学堂路' then '三乡镇'
        |-- when address rlike '湖西路' then '沙田镇'
        |when address rlike '碧云西段' then '沙溪镇'
        |when address rlike '工业大道' then '沙溪镇'
        |when address rlike '申明' then '沙溪镇'
        |when address rlike '悦云路' then '沙溪镇'
        |when address rlike '恒信花园' then '石岐街道'
        |when address rlike '宏基路' then '石岐街道'
        |when address rlike '进兴路' then '石岐街道'
        |when address rlike '假日广场' then '石岐街道'
        |when address rlike '石歧区' then '石岐街道'
        |when address rlike '青溪路162号' then '石岐街道'
        |when address rlike '康华路' then '石岐街道'
        |when address rlike '莲塘北路' then '石岐街道'
        |when address rlike '莲园' then '石岐街道'
        |when address rlike '顺景花园' then '石岐街道'
        |when address rlike '怡华苑' then '石岐街道'
        |when address rlike '碧安路' then '坦洲镇'
        |when address rlike '冲尾' then '坦洲镇'
        |when address rlike '翠园居' then '坦洲镇'
        |when address rlike '德秀路' then '坦洲镇'
        |when address rlike '嘉惠路' then '坦洲镇'
        |when address rlike '界狮' then '坦洲镇'
        |when address rlike '金山城' then '坦洲镇'
        |when address rlike '金源路' then '坦洲镇'
        |when address rlike '南坦路' then '坦洲镇'
        |when address rlike '沙贝路' then '坦洲镇'
        |when address rlike '泰湖路' then '坦洲镇'
        |when address rlike '坦神南路' then '坦洲镇'
        |when address rlike '网髻' then '坦洲镇'
        |when address rlike '怡康下街' then '坦洲镇'
        |when address rlike '源泰街' then '坦洲镇'
        |when address rlike '祯祥' then '坦洲镇'
        |when address rlike '金华中路' then '西区街道'
        |when address rlike '金叶广场' then '西区街道'
        |when address rlike '隆畅市场' then '西区街道'
        |when address rlike '安定' then '小榄镇'
        |when address rlike '广泽街' then '小榄镇'
        |when address rlike '环城南路' then '小榄镇'
        |when address rlike '聚新路' then '小榄镇'
        |when address rlike '联丰' then '小榄镇'
        |when address rlike '民安中路' then '小榄镇'
        |when address rlike '泰丰' then '小榄镇'
        |when address rlike '同胜' then '小榄镇'
        |when address rlike '文成东路' then '小榄镇'
        |when address rlike '祥丰南路' then '小榄镇'
        |when address rlike '新都汇' then '小榄镇'
        |when address rlike '新华街' then '小榄镇'
        |when address rlike '幸福' then '小榄镇'
        |when address rlike '永宁' then '小榄镇'
        |when address rlike '长堤路' then '小榄镇'
        |when address rlike '竹源' then '小榄镇'
        |when address rlike '钻石' then '小榄镇'
        |when address rlike '庵前' then '中山火炬高技术产业开发区'
        |when address rlike '高新' then '中山火炬高技术产业开发区'
        |when address rlike '康乐大道' then '中山火炬高技术产业开发区'
        |when address rlike '陵园路' then '中山火炬高技术产业开发区'
        |when address rlike '名嘉花园' then '中山火炬高技术产业开发区'
        |when address rlike '同乐东路' then '中山火炬高技术产业开发区'
        |when address rlike '沿江东' then '中山火炬高技术产业开发区'
        |when address rlike '银苑' then '中山火炬高技术产业开发区'
        |else district end)
        |when city='东莞市' then (
        |case
        |when address rlike '东正' then '莞城街道'
        |when address rlike '市桥' then '莞城街道'
        |when address rlike '西隅' then '莞城街道'
        |when address rlike '北隅' then '莞城街道'
        |when address rlike '罗沙' then '莞城街道'
        |when address rlike '兴塘' then '莞城街道'
        |when address rlike '博厦' then '莞城街道'
        |when address rlike '创业' then '莞城街道'
        |when address rlike '鸿福' then '南城街道'
        |when address rlike '宏远' then '南城街道'
        |when address rlike '胜和' then '南城街道'
        |when address rlike '元美' then '南城街道'
        |when address rlike '亨美' then '南城街道'
        |when address rlike '三元里' then '南城街道'
        |when address rlike '篁' then '南城街道'
        |when address rlike '新基' then '南城街道'
        |when address rlike '周溪' then '南城街道'
        |when address rlike '袁屋边' then '南城街道'
        |when address rlike '白马' then '南城街道'
        |when address rlike '石鼓' then '南城街道'
        |when address rlike '蛤地' then '南城街道'
        |when address rlike '西平' then '南城街道'
        |when address rlike '雅园' then '南城街道'
        |when address rlike '水濂' then '南城街道'
        |when address rlike '新城' then '南城街道'
        |when address rlike '宏图' then '南城街道'
        |when address rlike '岗贝' then '东城街道'
        |when address rlike '世纪广场' then '东城街道'
        |when address rlike '泰和商业街' then '东城街道'
        |when address rlike '花园新' then '东城街道'
        |when address rlike '东泰' then '东城街道'
        |when address rlike '温塘' then '东城街道'
        |when address rlike '桑园' then '东城街道'
        |when address rlike '周屋' then '东城街道'
        |when address rlike '余屋' then '东城街道'
        |when address rlike '鳌峙塘' then '东城街道'
        |when address rlike '峡口' then '东城街道'
        |when address rlike '柏洲边' then '东城街道'
        |when address rlike '上桥' then '东城街道'
        |when address rlike '下桥' then '东城街道'
        |when address rlike '樟 ' then '东城街道'
        |when address rlike '梨川' then '东城街道'
        |when address rlike '堑头 ' then '东城街道'
        |when address rlike '主山' then '东城街道'
        |when address rlike '石井' then '东城街道'
        |when address rlike '同沙' then '东城街道'
        |when address rlike '光明 ' then '东城街道'
        |when address rlike '牛山' then '东城街道'
        |when address rlike '立新' then '东城街道'
        |when address rlike '火炼树' then '东城街道'
        |when address rlike '星城' then '东城街道'
        |when address rlike '万江' then '万江街道'
        |when address rlike '万江墟' then '万江街道'
        |when address rlike '石美' then '万江街道'
        |when address rlike '莫屋' then '万江街道'
        |when address rlike '拔蛟窝' then '万江街道'
        |when address rlike '黄粘洲' then '万江街道'
        |when address rlike '蚬涌' then '万江街道'
        |when address rlike '谷涌' then '万江街道'
        |when address rlike '小享' then '万江街道'
        |when address rlike '滘联' then '万江街道'
        |when address rlike '金泰' then '万江街道'
        |when address rlike '曲海' then '万江街道'
        |when address rlike '牌楼基' then '万江街道'
        |when address rlike '大莲塘' then '万江街道'
        |when address rlike '水蛇涌' then '万江街道'
        |when address rlike '共联' then '万江街道'
        |when address rlike '新谷涌' then '万江街道'
        |when address rlike '坝头' then '万江街道'
        |when address rlike '胜利' then '万江街道'
        |when address rlike '官桥滘' then '万江街道'
        |when address rlike '简沙洲' then '万江街道'
        |when address rlike '新和' then '万江街道'
        |when address rlike '上甲' then '万江街道'
        |when address rlike '严屋' then '万江街道'
        |when address rlike '大汾' then '万江街道'
        |when address rlike '新' then '万江街道'
        |when address rlike '流涌尾' then '万江街道'
        |when address rlike '新城' then '万江街道'
        |when address rlike '中山东' then '石龙镇'
        |when address rlike '中山西' then '石龙镇'
        |when address rlike '兴龙' then '石龙镇'
        |when address rlike '西湖' then '石龙镇'
        |when address rlike '忠维' then '石龙镇'
        |when address rlike '林屋' then '石龙镇'
        |when address rlike '蒲溪' then '石龙镇'
        |when address rlike '新维' then '石龙镇'
        |when address rlike '王屋洲' then '石龙镇'
        |when address rlike '黄家山' then '石龙镇'
        |when address rlike '横山' then '石排镇'
        |when address rlike '田寮' then '石排镇'
        |when address rlike '石排' then '石排镇'
        |when address rlike '下沙' then '石排镇'
        |when address rlike '埔心' then '石排镇'
        |when address rlike '水贝' then '石排镇'
        |when address rlike '福隆' then '石排镇'
        |when address rlike '谷吓' then '石排镇'
        |when address rlike '向西' then '石排镇'
        |when address rlike '庙边王' then '石排镇'
        |when address rlike '塘尾' then '石排镇'
        |when address rlike '燕窝' then '石排镇'
        |when address rlike '沙角' then '石排镇'
        |when address rlike '李家坊' then '石排镇'
        |when address rlike '赤坎' then '石排镇'
        |when address rlike '黄家壆' then '石排镇'
        |when address rlike '田边' then '石排镇'
        |when address rlike '中坑' then '石排镇'
        |when address rlike '太和' then '石排镇'
        |when address rlike '茶山圩' then '茶山镇'
        |when address rlike '茶溪' then '茶山镇'
        |when address rlike '上元' then '茶山镇'
        |when address rlike '茶山' then '茶山镇'
        |when address rlike '下朗' then '茶山镇'
        |when address rlike '横江' then '茶山镇'
        |when address rlike '卢边' then '茶山镇'
        |when address rlike '寒溪水' then '茶山镇'
        |when address rlike '增埗' then '茶山镇'
        |when address rlike '南社' then '茶山镇'
        |when address rlike '塘角' then '茶山镇'
        |when address rlike '京山' then '茶山镇'
        |when address rlike '博头' then '茶山镇'
        |when address rlike '冲美' then '茶山镇'
        |when address rlike '粟边' then '茶山镇'
        |when address rlike '刘黄' then '茶山镇'
        |when address rlike '孙屋' then '茶山镇'
        |when address rlike '超朗' then '茶山镇'
        |when address rlike '铁岗' then '企石镇'
        |when address rlike '深巷' then '企石镇'
        |when address rlike '湖美' then '企石镇'
        |when address rlike '博厦' then '企石镇'
        |when address rlike '上洞' then '企石镇'
        |when address rlike '清湖' then '企石镇'
        |when address rlike '江边' then '企石镇'
        |when address rlike '旧围' then '企石镇'
        |when address rlike '东平' then '企石镇'
        |when address rlike '上截' then '企石镇'
        |when address rlike '下截' then '企石镇'
        |when address rlike '东山' then '企石镇'
        |when address rlike '铁炉坑' then '企石镇'
        |when address rlike '企石' then '企石镇'
        |when address rlike '杨屋' then '企石镇'
        |when address rlike '莫屋' then '企石镇'
        |when address rlike '霞朗' then '企石镇'
        |when address rlike '新南' then '企石镇'
        |when address rlike '南坑' then '企石镇'
        |when address rlike '宝石' then '企石镇'
        |when address rlike '莲城' then '桥头镇'
        |when address rlike '桥头' then '桥头镇'
        |when address rlike '迳联' then '桥头镇'
        |when address rlike '田新' then '桥头镇'
        |when address rlike '岭头' then '桥头镇'
        |when address rlike '大洲' then '桥头镇'
        |when address rlike '东江' then '桥头镇'
        |when address rlike '山和' then '桥头镇'
        |when address rlike '屋厦' then '桥头镇'
        |when address rlike '岗头' then '桥头镇'
        |when address rlike '李屋' then '桥头镇'
        |when address rlike '邓屋' then '桥头镇'
        |when address rlike '朗厦' then '桥头镇'
        |when address rlike '邵岗头' then '桥头镇'
        |when address rlike '石水口' then '桥头镇'
        |when address rlike '禾坑' then '桥头镇'
        |when address rlike '田头角' then '桥头镇'
        |when address rlike '东坑' then '东坑镇'
        |when address rlike '寮边头' then '东坑镇'
        |when address rlike '长安塘' then '东坑镇'
        |when address rlike '黄麻岭' then '东坑镇'
        |when address rlike '塔岗' then '东坑镇'
        |when address rlike '井美' then '东坑镇'
        |when address rlike '新门楼' then '东坑镇'
        |when address rlike '初坑' then '东坑镇'
        |when address rlike '凤大' then '东坑镇'
        |when address rlike '丁屋' then '东坑镇'
        |when address rlike '彭屋' then '东坑镇'
        |when address rlike '黄屋' then '东坑镇'
        |when address rlike '角社' then '东坑镇'
        |when address rlike '石涌' then '横沥镇'
        |when address rlike '隔坑' then '横沥镇'
        |when address rlike '半仙山' then '横沥镇'
        |when address rlike '横沥' then '横沥镇'
        |when address rlike '田头' then '横沥镇'
        |when address rlike '田坑' then '横沥镇'
        |when address rlike '头' then '横沥镇'
        |when address rlike '长巷' then '横沥镇'
        |when address rlike '田饶步' then '横沥镇'
        |when address rlike '六甲' then '横沥镇'
        |when address rlike '尾' then '横沥镇'
        |when address rlike '水边' then '横沥镇'
        |when address rlike '新四' then '横沥镇'
        |when address rlike '山厦' then '横沥镇'
        |when address rlike '月塘' then '横沥镇'
        |when address rlike '张坑' then '横沥镇'
        |when address rlike '恒泉' then '横沥镇'
        |when address rlike '岗梓' then '常平镇'
        |when address rlike '国江家具有限公司' then '常平镇'
        |when address rlike '塘角' then '常平镇'
        |when address rlike '苏坑' then '常平镇'
        |when address rlike '袁山贝' then '常平镇'
        |when address rlike '金美' then '常平镇'
        |when address rlike '还珠沥' then '常平镇'
        |when address rlike '朗贝' then '常平镇'
        |when address rlike '桥沥' then '常平镇'
        |when address rlike '卢屋' then '常平镇'
        |when address rlike '九江水' then '常平镇'
        |when address rlike '朗洲' then '常平镇'
        |when address rlike '陈屋贝' then '常平镇'
        |when address rlike '司马' then '常平镇'
        |when address rlike '霞坑' then '常平镇'
        |when address rlike '漱旧' then '常平镇'
        |when address rlike '漱新' then '常平镇'
        |when address rlike '黄泥塘' then '常平镇'
        |when address rlike '元江元' then '常平镇'
        |when address rlike '横江厦' then '常平镇'
        |when address rlike '沙湖口' then '常平镇'
        |when address rlike '白石岗' then '常平镇'
        |when address rlike '松柏塘' then '常平镇'
        |when address rlike '上坑' then '常平镇'
        |when address rlike '木棆' then '常平镇'
        |when address rlike '下墟' then '常平镇'
        |when address rlike '板石' then '常平镇'
        |when address rlike '田尾' then '常平镇'
        |when address rlike '白花沥' then '常平镇'
        |when address rlike '桥梓' then '常平镇'
        |when address rlike '麦元' then '常平镇'
        |when address rlike '土塘' then '常平镇'
        |when address rlike '常平' then '常平镇'
        |when address rlike '新民' then '常平镇'
        |when address rlike '人民中路' then '虎门镇'
        |when address rlike '树田' then '虎门镇'
        |when address rlike '怀德' then '虎门镇'
        |when address rlike '路东' then '虎门镇'
        |when address rlike '南栅' then '虎门镇'
        |when address rlike '东方' then '虎门镇'
        |when address rlike '东风' then '虎门镇'
        |when address rlike '白沙' then '虎门镇'
        |when address rlike '赤岗' then '虎门镇'
        |when address rlike '金州' then '虎门镇'
        |when address rlike '龙眼' then '虎门镇'
        |when address rlike '南面' then '虎门镇'
        |when address rlike '小捷滘' then '虎门镇'
        |when address rlike '新湾' then '虎门镇'
        |when address rlike '宴岗' then '虎门镇'
        |when address rlike '沙角' then '虎门镇'
        |when address rlike '大宁' then '虎门镇'
        |when address rlike '黄' then '虎门镇'
        |when address rlike '居岐' then '虎门镇'
        |when address rlike '则徐' then '虎门镇'
        |when address rlike '镇口' then '虎门镇'
        |when address rlike '头' then '虎门镇'
        |when address rlike '民泰' then '虎门镇'
        |when address rlike '陈' then '虎门镇'
        |when address rlike '北栅' then '虎门镇'
        |when address rlike '博涌' then '虎门镇'
        |when address rlike '新联' then '虎门镇'
        |when address rlike '虎门寨' then '虎门镇'
        |when address rlike '九门寨' then '虎门镇'
        |when address rlike '武山沙' then '虎门镇'
        |when address rlike '北面' then '虎门镇'
        |when address rlike '上角' then '长安镇'
        |when address rlike '厦边' then '长安镇'
        |when address rlike '厦岗' then '长安镇'
        |when address rlike '上沙' then '长安镇'
        |when address rlike '沙头' then '长安镇'
        |when address rlike '乌沙' then '长安镇'
        |when address rlike '锦厦' then '长安镇'
        |when address rlike '新民' then '长安镇'
        |when address rlike '涌头' then '长安镇'
        |when address rlike '咸西' then '长安镇'
        |when address rlike '长福东街' then '长安镇'
        |when address rlike '长盛' then '长安镇'
        |when address rlike '宵边' then '长安镇'
        |when address rlike '新安' then '长安镇'
        |when address rlike '中围' then '沙田镇'
        |when address rlike '和安' then '沙田镇'
        |when address rlike '大流' then '沙田镇'
        |when address rlike '泥洲' then '沙田镇'
        |when address rlike '杨公洲' then '沙田镇'
        |when address rlike '斜西' then '沙田镇'
        |when address rlike '民田' then '沙田镇'
        |when address rlike '福禄沙' then '沙田镇'
        |when address rlike '先锋' then '沙田镇'
        |when address rlike '大坭' then '沙田镇'
        |when address rlike '齐沙' then '沙田镇'
        |when address rlike '穗丰年' then '沙田镇'
        |when address rlike '西大坦' then '沙田镇'
        |when address rlike '稔洲' then '沙田镇'
        |when address rlike '义沙' then '沙田镇'
        |when address rlike '西太隆' then '沙田镇'
        |when address rlike '横流' then '沙田镇'
        |when address rlike '滨港' then '沙田镇'
        |when address rlike '厚街' then '厚街镇'
        |when address rlike '涌口' then '厚街镇'
        |when address rlike '珊美' then '厚街镇'
        |when address rlike '寮厦' then '厚街镇'
        |when address rlike '桥头' then '厚街镇'
        |when address rlike '河田' then '厚街镇'
        |when address rlike '赤岭' then '厚街镇'
        |when address rlike '三屯' then '厚街镇'
        |when address rlike '宝屯' then '厚街镇'
        |when address rlike '陈屋' then '厚街镇'
        |when address rlike '新塘' then '厚街镇'
        |when address rlike '环冈' then '厚街镇'
        |when address rlike '宝塘' then '厚街镇'
        |when address rlike '溪头' then '厚街镇'
        |when address rlike '南五' then '厚街镇'
        |when address rlike '白濠' then '厚街镇'
        |when address rlike '下汴' then '厚街镇'
        |when address rlike '大迳' then '厚街镇'
        |when address rlike '汀山' then '厚街镇'
        |when address rlike '双岗' then '厚街镇'
        |when address rlike '沙塘' then '厚街镇'
        |when address rlike '新围' then '厚街镇'
        |when address rlike '竹溪' then '厚街镇'
        |when address rlike '湖景' then '厚街镇'
        |when address rlike '寮步' then '寮步镇'
        |when address rlike '塘边' then '寮步镇'
        |when address rlike '东莞市旭达停车场' then '寮步镇'
        |when address rlike '良边' then '寮步镇'
        |when address rlike '石龙坑' then '寮步镇'
        |when address rlike '坑口' then '寮步镇'
        |when address rlike '泉塘' then '寮步镇'
        |when address rlike '横坑' then '寮步镇'
        |when address rlike '竹园' then '寮步镇'
        |when address rlike '下岭贝' then '寮步镇'
        |when address rlike '岭厦' then '寮步镇'
        |when address rlike '霞边' then '寮步镇'
        |when address rlike '新旧围' then '寮步镇'
        |when address rlike '上屯' then '寮步镇'
        |when address rlike '凫山' then '寮步镇'
        |when address rlike '西溪' then '寮步镇'
        |when address rlike '石步' then '寮步镇'
        |when address rlike '富竹山' then '寮步镇'
        |when address rlike '塘唇' then '寮步镇'
        |when address rlike '向西' then '寮步镇'
        |when address rlike '缪边' then '寮步镇'
        |when address rlike '牛杨' then '寮步镇'
        |when address rlike '上底' then '寮步镇'
        |when address rlike '刘屋冚' then '寮步镇'
        |when address rlike '浮竹山' then '寮步镇'
        |when address rlike '药勒' then '寮步镇'
        |when address rlike '陈家埔' then '寮步镇'
        |when address rlike '小坑' then '寮步镇'
        |when address rlike '井巷' then '寮步镇'
        |when address rlike '长坑' then '寮步镇'
        |when address rlike '良平' then '寮步镇'
        |when address rlike '大岭山' then '大岭山镇'
        |when address rlike '农场' then '大岭山镇'
        |when address rlike '马蹄岗' then '大岭山镇'
        |when address rlike '连平' then '大岭山镇'
        |when address rlike '新塘' then '大岭山镇'
        |when address rlike '杨屋' then '大岭山镇'
        |when address rlike '大环' then '大岭山镇'
        |when address rlike '大岭' then '大岭山镇'
        |when address rlike '大片美' then '大岭山镇'
        |when address rlike '金桔' then '大岭山镇'
        |when address rlike '梅林' then '大岭山镇'
        |when address rlike '大沙' then '大岭山镇'
        |when address rlike '元岭' then '大岭山镇'
        |when address rlike '大塘' then '大岭山镇'
        |when address rlike '矮岭冚' then '大岭山镇'
        |when address rlike '大塘朗' then '大岭山镇'
        |when address rlike '鸡翅岭' then '大岭山镇'
        |when address rlike '下高田' then '大岭山镇'
        |when address rlike '百花洞' then '大岭山镇'
        |when address rlike '旧飞鹅' then '大岭山镇'
        |when address rlike '水朗' then '大岭山镇'
        |when address rlike '颜屋' then '大岭山镇'
        |when address rlike '大朗' then '大朗镇'
        |when address rlike '大井头' then '大朗镇'
        |when address rlike '长塘' then '大朗镇'
        |when address rlike '黄草朗' then '大朗镇'
        |when address rlike '佛新' then '大朗镇'
        |when address rlike '求富路' then '大朗镇'
        |when address rlike '屏山' then '大朗镇'
        |when address rlike '巷头' then '大朗镇'
        |when address rlike '圣堂' then '大朗镇'
        |when address rlike '巷尾' then '大朗镇'
        |when address rlike '竹山' then '大朗镇'
        |when address rlike '长富' then '大朗镇'
        |when address rlike '高英' then '大朗镇'
        |when address rlike '蔡边' then '大朗镇'
        |when address rlike '沙步' then '大朗镇'
        |when address rlike '佛子凹' then '大朗镇'
        |when address rlike '石厦' then '大朗镇'
        |when address rlike '黎贝岭' then '大朗镇'
        |when address rlike '水口' then '大朗镇'
        |when address rlike '松柏朗' then '大朗镇'
        |when address rlike '水平' then '大朗镇'
        |when address rlike '松木山' then '大朗镇'
        |when address rlike '杨涌' then '大朗镇'
        |when address rlike '犀牛陂' then '大朗镇'
        |when address rlike '新马莲' then '大朗镇'
        |when address rlike '洋乌' then '大朗镇'
        |when address rlike '洋坑塘' then '大朗镇'
        |when address rlike '宝陂' then '大朗镇'
        |when address rlike '新市' then '黄江镇'
        |when address rlike '梅塘' then '黄江镇'
        |when address rlike '田美' then '黄江镇'
        |when address rlike '长龙' then '黄江镇'
        |when address rlike '北岸' then '黄江镇'
        |when address rlike '三新' then '黄江镇'
        |when address rlike '宝山' then '黄江镇'
        |when address rlike '百果洞' then '樟木头镇'
        |when address rlike '樟洋' then '樟木头镇'
        |when address rlike '裕丰' then '樟木头镇'
        |when address rlike '柏地' then '樟木头镇'
        |when address rlike '官仓' then '樟木头镇'
        |when address rlike '圩镇' then '樟木头镇'
        |when address rlike '石新' then '樟木头镇'
        |when address rlike '金河' then '樟木头镇'
        |when address rlike '樟罗' then '樟木头镇'
        |when address rlike '樟新' then '樟木头镇'
        |when address rlike '泰园' then '谢岗镇'
        |when address rlike '窑山' then '谢岗镇'
        |when address rlike '大龙' then '谢岗镇'
        |when address rlike '黎' then '谢岗镇'
        |when address rlike '曹乐' then '谢岗镇'
        |when address rlike '五星' then '谢岗镇'
        |when address rlike '南面' then '谢岗镇'
        |when address rlike '赵林' then '谢岗镇'
        |when address rlike '谢山' then '谢岗镇'
        |when address rlike '谢岗' then '谢岗镇'
        |when address rlike '稔子园' then '谢岗镇'
        |when address rlike '大厚' then '谢岗镇'
        |when address rlike '林' then '塘厦镇'
        |when address rlike '利邦塑胶' then '塘厦镇'
        |when address rlike '莲湖' then '塘厦镇'
        |when address rlike '石潭埔' then '塘厦镇'
        |when address rlike '横塘' then '塘厦镇'
        |when address rlike '振兴围' then '塘厦镇'
        |when address rlike '诸佛岭' then '塘厦镇'
        |when address rlike '莆心湖' then '塘厦镇'
        |when address rlike '大坪' then '塘厦镇'
        |when address rlike '四' then '塘厦镇'
        |when address rlike '田心' then '塘厦镇'
        |when address rlike '龙背岭' then '塘厦镇'
        |when address rlike '蛟乙塘' then '塘厦镇'
        |when address rlike '沙湖' then '塘厦镇'
        |when address rlike '平山' then '塘厦镇'
        |when address rlike '清湖头' then '塘厦镇'
        |when address rlike '石鼓' then '塘厦镇'
        |when address rlike '石马' then '塘厦镇'
        |when address rlike '桥陇' then '塘厦镇'
        |when address rlike '凤凰岗' then '塘厦镇'
        |when address rlike '重河' then '清溪镇'
        |when address rlike '大埔' then '清溪镇'
        |when address rlike '土桥' then '清溪镇'
        |when address rlike '谢坑' then '清溪镇'
        |when address rlike '荔横' then '清溪镇'
        |when address rlike '清厦' then '清溪镇'
        |when address rlike '大利' then '清溪镇'
        |when address rlike '九乡' then '清溪镇'
        |when address rlike '铁场' then '清溪镇'
        |when address rlike '铁松' then '清溪镇'
        |when address rlike '青皇' then '清溪镇'
        |when address rlike '三星' then '清溪镇'
        |when address rlike '厦泥' then '清溪镇'
        |when address rlike '三中' then '清溪镇'
        |when address rlike '上元' then '清溪镇'
        |when address rlike '松岗' then '清溪镇'
        |when address rlike '浮岗' then '清溪镇'
        |when address rlike '罗马' then '清溪镇'
        |when address rlike '长山头' then '清溪镇'
        |when address rlike '渔梁围' then '清溪镇'
        |when address rlike '清溪' then '清溪镇'
        |when address rlike '雁田' then '凤岗镇'
        |when address rlike '官井头' then '凤岗镇'
        |when address rlike '油甘埔' then '凤岗镇'
        |when address rlike '凤德岭' then '凤岗镇'
        |when address rlike '塘沥' then '凤岗镇'
        |when address rlike '黄洞' then '凤岗镇'
        |when address rlike '竹塘' then '凤岗镇'
        |when address rlike '竹尾田' then '凤岗镇'
        |when address rlike '三联' then '凤岗镇'
        |when address rlike '五联' then '凤岗镇'
        |when address rlike '天堂围' then '凤岗镇'
        |when address rlike '凤岗' then '凤岗镇'
        |when address rlike '麻一' then '麻涌镇'
        |when address rlike '麻三' then '麻涌镇'
        |when address rlike '麻四' then '麻涌镇'
        |when address rlike '漳澎' then '麻涌镇'
        |when address rlike '大步' then '麻涌镇'
        |when address rlike '东太' then '麻涌镇'
        |when address rlike '新基' then '麻涌镇'
        |when address rlike '川槎' then '麻涌镇'
        |when address rlike '鸥涌' then '麻涌镇'
        |when address rlike '黎滘' then '麻涌镇'
        |when address rlike '华阳' then '麻涌镇'
        |when address rlike '南洲' then '麻涌镇'
        |when address rlike '大盛' then '麻涌镇'
        |when address rlike '麻二' then '麻涌镇'
        |when address rlike '麻涌' then '麻涌镇'
        |when address rlike '中堂' then '中堂镇'
        |when address rlike '斗朗' then '中堂镇'
        |when address rlike '江南' then '中堂镇'
        |when address rlike '东泊' then '中堂镇'
        |when address rlike '红锋' then '中堂镇'
        |when address rlike '潢涌' then '中堂镇'
        |when address rlike '三涌' then '中堂镇'
        |when address rlike '湛翠' then '中堂镇'
        |when address rlike '凤冲' then '中堂镇'
        |when address rlike '袁家涌' then '中堂镇'
        |when address rlike '吴家涌' then '中堂镇'
        |when address rlike '鹤田' then '中堂镇'
        |when address rlike '中堂' then '中堂镇'
        |when address rlike '东向' then '中堂镇'
        |when address rlike '蕉利' then '中堂镇'
        |when address rlike '槎滘' then '中堂镇'
        |when address rlike '下芦' then '中堂镇'
        |when address rlike '马沥' then '中堂镇'
        |when address rlike '四乡' then '中堂镇'
        |when address rlike '宝莲' then '高埗镇'
        |when address rlike '朱磡' then '高埗镇'
        |when address rlike '新联' then '高埗镇'
        |when address rlike '冼沙 ' then '高埗镇'
        |when address rlike '下江城' then '高埗镇'
        |when address rlike '塘厦' then '高埗镇'
        |when address rlike '上江城' then '高埗镇'
        |when address rlike '三联' then '高埗镇'
        |when address rlike '欧邓' then '高埗镇'
        |when address rlike '芦' then '高埗镇'
        |when address rlike '卢溪' then '高埗镇'
        |when address rlike '凌屋 ' then '高埗镇'
        |when address rlike '护安围' then '高埗镇'
        |when address rlike '横滘头' then '高埗镇'
        |when address rlike '高埗' then '高埗镇'
        |when address rlike '低涌' then '高埗镇'
        |when address rlike '草墩' then '高埗镇'
        |when address rlike '保安围' then '高埗镇'
        |when address rlike '新创' then '高埗镇'
        |when address rlike '石碣' then '石碣镇'
        |when address rlike '唐洪' then '石碣镇'
        |when address rlike '水南' then '石碣镇'
        |when address rlike '石碣' then '石碣镇'
        |when address rlike '刘屋' then '石碣镇'
        |when address rlike '横滘' then '石碣镇'
        |when address rlike '鹤田厦' then '石碣镇'
        |when address rlike '四甲' then '石碣镇'
        |when address rlike '沙腰' then '石碣镇'
        |when address rlike '梁家' then '石碣镇'
        |when address rlike '桔洲' then '石碣镇'
        |when address rlike '单屋' then '石碣镇'
        |when address rlike '涌口' then '石碣镇'
        |when address rlike '西南' then '石碣镇'
        |when address rlike '黄泗围' then '石碣镇'
        |when address rlike '上合' then '望牛墩镇'
        |when address rlike '寮厦' then '望牛墩镇'
        |when address rlike '李屋' then '望牛墩镇'
        |when address rlike '聚龙江' then '望牛墩镇'
        |when address rlike '锦涡' then '望牛墩镇'
        |when address rlike '横沥' then '望牛墩镇'
        |when address rlike '杜屋' then '望牛墩镇'
        |when address rlike '赤滘' then '望牛墩镇'
        |when address rlike '芙蓉沙' then '望牛墩镇'
        |when address rlike '官桥涌' then '望牛墩镇'
        |when address rlike '福安' then '望牛墩镇'
        |when address rlike '洲湾' then '望牛墩镇'
        |when address rlike '朱平沙' then '望牛墩镇'
        |when address rlike '下漕' then '望牛墩镇'
        |when address rlike '望东' then '望牛墩镇'
        |when address rlike '洲涡' then '望牛墩镇'
        |when address rlike '五涌' then '望牛墩镇'
        |when address rlike '望联' then '望牛墩镇'
        |when address rlike '石排' then '望牛墩镇'
        |when address rlike '官洲' then '望牛墩镇'
        |when address rlike '扶涌' then '望牛墩镇'
        |when address rlike '望牛墩' then '望牛墩镇'
        |when address rlike '洪屋涡' then '洪梅镇'
        |when address rlike '新庄' then '洪梅镇'
        |when address rlike '梅沙' then '洪梅镇'
        |when address rlike '氹涌' then '洪梅镇'
        |when address rlike '夏汇' then '洪梅镇'
        |when address rlike '黎洲角' then '洪梅镇'
        |when address rlike '尧均' then '洪梅镇'
        |when address rlike '乌沙' then '洪梅镇'
        |when address rlike '金鳌沙' then '洪梅镇'
        |when address rlike '洪梅' then '洪梅镇'
        |when address rlike '兴隆' then '道滘镇'
        |when address rlike '南城' then '道滘镇'
        |when address rlike '闸口' then '道滘镇'
        |when address rlike '北永' then '道滘镇'
        |when address rlike '永庆' then '道滘镇'
        |when address rlike '厚德' then '道滘镇'
        |when address rlike '蔡白' then '道滘镇'
        |when address rlike '南丫' then '道滘镇'
        |when address rlike '九曲' then '道滘镇'
        |when address rlike '大罗沙' then '道滘镇'
        |when address rlike '小河' then '道滘镇'
        |when address rlike '昌平' then '道滘镇'
        |when address rlike '大岭丫' then '道滘镇'
        |when address rlike '大鱼沙' then '道滘镇'
        |when address rlike '茶南路' then '茶山镇'
        |when address rlike '城西吉街' then '茶山镇'
        |when address rlike '金丰' then '茶山镇'
        |when address rlike '龙田' then '茶山镇'
        |when address rlike '明德一街' then '茶山镇'
        |when address rlike '文化路' then '茶山镇'
        |when address rlike '兴隆路' then '茶山镇'
        |when address rlike '朝阳三街' then '常平镇'
        |when address rlike '东站路' then '常平镇'
        |when address rlike '丽景花园' then '常平镇'
        |when address rlike '南门路' then '常平镇'
        |when address rlike '园林路' then '常平镇'
        |when address rlike '碧湖街' then '大朗镇'
        |when address rlike '大井头' then '大朗镇'
        |when address rlike '富华' then '大朗镇'
        |when address rlike '富康' then '大朗镇'
        |when address rlike '富民' then '大朗镇'
        |when address rlike '富通路' then '大朗镇'
        |when address rlike '金海西' then '大朗镇'
        |when address rlike '黎贝岭村新围仔' then '大朗镇'
        |when address rlike '三星路' then '大朗镇'
        |when address rlike '红河大道' then '大朗镇'
        |when address rlike '上园街' then '大朗镇'
        |when address rlike '沃尔玛时代广场' then '大朗镇'
        |when address rlike '新围二街' then '大朗镇'
        |when address rlike '怡安路' then '大朗镇'
        |when address rlike '教育路' then '大岭山镇'
        |when address rlike '马蹄岗' then '大岭山镇'
        |when address rlike '西大路' then '大岭山镇'
        |when address rlike '中兴路' then '大岭山镇'
        |when address rlike '中央广场' then '南城区'
        |when address rlike '东城金街' then '东城区'
        |when address rlike '文华街道' then '东城区'
        |when address rlike '星河新天地' then '东城区'
        |when address rlike '益禾堂' then '东城区'
        |when address rlike '富强二期' then '东坑镇'
        |when address rlike '风岗镇官井头村' then '凤岗镇'
        |when address rlike '大运城邦四期' then '凤岗镇'
        |when address rlike '凤深大道' then '凤岗镇'
        |when address rlike '黄河西路' then '凤岗镇'
        |when address rlike '金凤凰市场' then '凤岗镇'
        |when address rlike '龙平南路' then '凤岗镇'
        |when address rlike '三联村' then '凤岗镇'
        |when address rlike '塘沥村' then '凤岗镇'
        |when address rlike '新世纪广场' then '凤岗镇'
        |when address rlike '雁田村' then '凤岗镇'
        |when address rlike '永和桥' then '凤岗镇'
        |when address rlike '永盛大街' then '凤岗镇'
        |when address rlike '油甘埔村' then '凤岗镇'
        |when address rlike '竹塘村' then '凤岗镇'
        |when address rlike '低涌黄二村' then '高埗镇'
        |when address rlike '高坡镇' then '高埗镇'
        |when address rlike '江城东路' then '高埗镇'
        |when address rlike '振兴南路' then '高埗镇'
        |when address rlike '东湖花园' then '莞城区'
        |when address rlike '地王广场' then '莞城区'
        |when address rlike '东市小吃' then '莞城区'
        |when address rlike '东正路' then '莞城区'
        |when address rlike '花园新村' then '莞城区'
        |when address rlike '向阳路' then '莞城区'
        |when address rlike '解放路' then '莞城区'
        |when address rlike '老邮电局' then '莞城区'
        |when address rlike '职工业余学校' then '莞城区'
        |when address rlike '大新商场' then '横沥镇'
        |when address rlike '东环路' then '横沥镇'
        |when address rlike '汉城' then '横沥镇'
        |when address rlike '育才路' then '横沥镇'
        |when address rlike '禾仓路' then '厚街镇'
        |when address rlike '南环路' then '厚街镇'
        |when address rlike '金山商业区' then '厚街镇'
        |when address rlike '康乐' then '厚街镇'
        |when address rlike '寮厦' then '厚街镇'
        |when address rlike '南丰苑' then '厚街镇'
        |when address rlike '珊瑚路' then '厚街镇'
        |when address rlike '体育路' then '厚街镇'
        |when address rlike '沿河路' then '厚街镇'
        |when address rlike '永辉超市' then '厚街镇'
        |when address rlike '白沙三村' then '虎门镇'
        |when address rlike '陈村社区' then '虎门镇'
        |when address rlike '创业路' then '虎门镇'
        |when address rlike '凤凰东街' then '虎门镇'
        |when address rlike '栗香路' then '虎门镇'
        |when address rlike '豪景花园' then '虎门镇'
        |when address rlike '幸福路' then '虎门镇'
        |when address rlike '梁屋村' then '虎门镇'
        |when address rlike '龙眼路' then '虎门镇'
        |when address rlike '民主路' then '虎门镇'
        |when address rlike '宁江路' then '虎门镇'
        |when address rlike '仁德街' then '虎门镇'
        |when address rlike '新德园' then '虎门镇'
        |when address rlike '长德路' then '虎门镇'
        |when address rlike '长鸿路' then '虎门镇'
        |when address rlike '康宁一路' then '虎门镇'
        |when address rlike '朝阳街' then '黄江镇'
        |when address rlike '东方大道' then '黄江镇'
        |when address rlike '东升街' then '黄江镇'
        |when address rlike '康湖路' then '黄江镇'
        |when address rlike '五岳东路' then '黄江镇'
        |when address rlike '下三桥' then '黄江镇'
        |when address rlike '星晖' then '黄江镇'
        |when address rlike '东城路' then '寮步镇'
        |when address rlike '环城路' then '寮步镇'
        |when address rlike '金龙街' then '寮步镇'
        |when address rlike '老文化广场' then '寮步镇'
        |when address rlike '培正巷' then '寮步镇'
        |when address rlike '全兴' then '寮步镇'
        |when address rlike '泉兴街' then '寮步镇'
        |when address rlike '沙朗路' then '寮步镇'
        |when address rlike '松山湖' then '寮步镇'
        |when address rlike '湖区北部工业城' then '寮步镇'
        |when address rlike '星城国际' then '寮步镇'
        |when address rlike '振兴路' then '寮步镇'
        |when address rlike '大坪路' then '龙岗区'
        |when address rlike '金城街' then '龙岗区'
        |when address rlike '新桥路' then '麻涌镇'
        |when address rlike '大盛村' then '麻涌镇'
        |when address rlike '红星街' then '麻涌镇'
        |when address rlike '财富广场' then '南城区'
        |when address rlike '鸿福路' then '南城区'
        |when address rlike '建设路' then '南城区'
        |when address rlike '金域华府' then '南城区'
        |when address rlike '凯旋公寓' then '南城区'
        |when address rlike '胜和路' then '南城区'
        |when address rlike '英联环湖街' then '南城区'
        |when address rlike '天成一品' then '南城区'
        |when address rlike '阳光一小' then '南城区'
        |when address rlike '新城国际酒店' then '南城区'
        |when address rlike '新丰街' then '南城区'
        |when address rlike '银丰路' then '南城区'
        |when address rlike '华洲购物广场' then '企石镇'
        |when address rlike '康宁小区' then '桥头镇'
        |when address rlike '桥头广场' then '桥头镇'
        |when address rlike '长青路' then '桥头镇'
        |when address rlike '合群街' then '清溪镇'
        |when address rlike '莲湖街' then '清溪镇'
        |when address rlike '三中' then '清溪镇'
        |when address rlike '银湖商业街' then '清溪镇'
        |when address rlike '万隆购物广场' then '沙田镇'
        |when address rlike '福永街道' then '石碣镇'
        |when address rlike '鹤龙街' then '石碣镇'
        |when address rlike '百花路' then '石碣镇'
        |when address rlike '明珠路' then '石碣镇'
        |when address rlike '明珠西路' then '石碣镇'
        |when address rlike '前进路' then '石碣镇'
        |when address rlike '新风东路' then '石碣镇'
        |when address rlike '大华广场' then '石龙镇'
        |when address rlike '黄家山' then '石龙镇'
        |when address rlike '石龙' then '石龙镇'
        |when address rlike '天和百货' then '石龙镇'
        |when address rlike '豪升百货' then '石排镇'
        |when address rlike '横山路' then '石排镇'
        |when address rlike '下沙村' then '石排镇'
        |when address rlike '横塘社区' then '塘厦镇'
        |when address rlike '滹源东街' then '塘厦镇'
        |when address rlike '建盛综合市场' then '塘厦镇'
        |when address rlike '岚湖新村' then '塘厦镇'
        |when address rlike '莲湖' then '塘厦镇'
        |when address rlike '美食美刻蛋糕' then '塘厦镇'
        |when address rlike '民业街' then '塘厦镇'
        |when address rlike '平山广场' then '塘厦镇'
        |when address rlike '沙龙街' then '塘厦镇'
        |when address rlike '田心市场' then '塘厦镇'
        |when address rlike '万科生活广场' then '塘厦镇'
        |when address rlike '一碗香麻辣烫' then '塘厦镇'
        |when address rlike '裕民街' then '塘厦镇'
        |when address rlike '诸佛岭迎宾小区' then '塘厦镇'
        |when address rlike '第三中学' then '万江区'
        |when address rlike '梁苑路' then '万江区'
        |when address rlike '上东国际花园' then '万江区'
        |when address rlike '胜利街' then '万江区'
        |when address rlike '胜利综合市场' then '万江区'
        |when address rlike '万达广场' then '万江区'
        |when address rlike '万江路' then '万江区'
        |when address rlike '万江新城' then '万江区'
        |when address rlike '文明三街' then '樟木头镇'
        |when address rlike '新华路' then '樟木头镇'
        |when address rlike '银西街' then '樟木头镇'
        |when address rlike '东方步行街' then '长安镇'
        |when address rlike '东南街' then '长安镇'
        |when address rlike '河东一路' then '长安镇'
        |when address rlike '锦江路' then '长安镇'
        |when address rlike '莲湖公寓' then '长安镇'
        |when address rlike '新街' then '长安镇'
        |when address rlike '兴一路' then '长安镇'
        |when address rlike '涌头社区' then '长安镇'
        |when address rlike '长安购物公园' then '长安镇'
        |when address rlike '长青南路' then '长安镇'
        |when address rlike '长源街' then '长安镇'
        |when address rlike '滨江大道' then '中堂镇'
        |when address rlike '富荣街' then '中堂镇'
        |
        |else district end) else '-1' end town,
        |*
        |from
        |th1Table
      """.stripMargin

    val town_have_been_Match_2 = spark.sql(addressSQL_town)

    val matchTown: Dataset[Row] = town_have_been_Match_1
      .select("shopId", "city", "district", "district_origin", "town")
      .union(
        town_have_been_Match_2.select("shopId", "city", "district", "district_origin", "town")
      )
      .selectExpr("shopId",
        """
          |case
          |when city='东莞市' and district='0' then district
          |when city='中山市' and district='0' then district
          |when city='东莞市' and town!='-1' then '镇'
          |when city='中山市' and town!='-1' then '镇'
          |else district_origin end district_origin
        """.stripMargin, "town")

    val resultData = matchTown
      .join(dataFrame.drop("district_origin"), Seq("shopId"), "left")

    resultData
  }


  /**
    * 匹配 政府 ：中山东莞 镇 的 regionId   及 中山东莞的县镇相同
    *
    * @param spark
    * @param dataFrame
    * @return aedzId
    */
  def match_ZSDG_ID(spark: SparkSession, dataFrame: DataFrame): DataFrame = {

    dataFrame.createOrReplaceTempView("table_v_get_matchID_ZSDG")
    val resultData = spark.sql(
      """
        |select
        |*,
        |case
        |when city='东莞市' and town !='-1' then town
        |when city='中山市' and town !='-1' then town
        |else district end districts
        |from
        |(
        |select *,
        |case
        |when province='广东省' and	city='东莞市'	and town='虎门镇'                then	'441901'
        |when province='广东省' and	city='东莞市'	and town='南城区'                then	'441902'
        |when province='广东省' and	city='东莞市'	and town='长安镇'                then	'441903'
        |when province='广东省' and	city='东莞市'	and town='东城区'                then	'441904'
        |when province='广东省' and	city='东莞市'	and town='厚街镇'                then	'441905'
        |when province='广东省' and	city='东莞市'	and town='万江区'                then	'441906'
        |when province='广东省' and	city='东莞市'	and town='大朗镇'                then	'441907'
        |when province='广东省' and	city='东莞市'	and town='寮步镇'                then	'441908'
        |when province='广东省' and	city='东莞市'	and town='清溪镇'                then	'441909'
        |when province='广东省' and	city='东莞市'	and town='常平镇'                then	'441910'
        |when province='广东省' and	city='东莞市'	and town='莞城区'                then	'441911'
        |when province='广东省' and	city='东莞市'	and town='道滘镇'                then	'441912'
        |when province='广东省' and	city='东莞市'	and town='黄江镇'                then	'441913'
        |when province='广东省' and	city='东莞市'	and town='中堂镇'                then	'441914'
        |when province='广东省' and	city='东莞市'	and town='大岭山镇'                 then	'441915'
        |when province='广东省' and	city='东莞市'	and town='横沥镇'                then	'441916'
        |when province='广东省' and	city='东莞市'	and town='万江街道'                 then	'441917'
        |when province='广东省' and	city='东莞市'	and town='塘厦镇'                then	'441918'
        |when province='广东省' and	city='东莞市'	and town='樟木头镇'                 then	'441919'
        |when province='广东省' and	city='东莞市'	and town='沙田镇'                then	'441920'
        |when province='广东省' and	city='东莞市'	and town='石碣镇'                then	'441921'
        |when province='广东省' and	city='东莞市'	and town='茶山镇'                then	'441922'
        |when province='广东省' and	city='东莞市'	and town='企石镇'                then	'441923'
        |when province='广东省' and	city='东莞市'	and town='石排镇'                then	'441924'
        |when province='广东省' and	city='东莞市'	and town='桥头镇'                then	'441925'
        |when province='广东省' and	city='东莞市'	and town='凤岗镇'                then	'441926'
        |when province='广东省' and	city='东莞市'	and town='石龙镇'                then	'441927'
        |when province='广东省' and	city='东莞市'	and town='高埗镇'                then	'441928'
        |when province='广东省' and	city='东莞市'	and town='东坑镇'                then	'441929'
        |when province='广东省' and	city='东莞市'	and town='麻涌镇'                then	'441930'
        |when province='广东省' and	city='东莞市'	and town='南城街道'                 then	'441931'
        |when province='广东省' and	city='东莞市'	and town='望牛墩镇'                 then	'441932'
        |when province='广东省' and	city='东莞市'	and town='谢岗镇'                then	'441933'
        |when province='广东省' and	city='东莞市'	and town='洪梅镇'                then	'441934'
        |when province='广东省' and	city='东莞市'	and town='东城街道'                 then	'441935'
        |when province='广东省' and	city='东莞市'	and town='莞城街道'                 then	'441936'
        |
        |when province='广东省' and	city='中山市'	and town='古镇镇'                then	'442001'
        |when province='广东省' and	city='中山市'	and town='小榄镇'                then	'442002'
        |when province='广东省' and	city='中山市'	and town='横栏镇'                then	'442003'
        |when province='广东省' and	city='中山市'	and town='沙溪镇'                then	'442004'
        |when province='广东省' and	city='中山市'	and town='坦洲镇'                then	'442005'
        |when province='广东省' and	city='中山市'	and town='中山火炬高技术产业开发区'                 then	'442006'
        |when province='广东省' and	city='中山市'	and town='石岐街道'                 then	'442007'
        |when province='广东省' and	city='中山市'	and town='东升镇'                then	'442008'
        |when province='广东省' and	city='中山市'	and town='三乡镇'                then	'442009'
        |when province='广东省' and	city='中山市'	and town='东凤镇'                then	'442010'
        |when province='广东省' and	city='中山市'	and town='大涌镇'                then	'442011'
        |when province='广东省' and	city='中山市'	and town='南头镇'                then	'442012'
        |when province='广东省' and	city='中山市'	and town='西区街道'                 then	'442013'
        |when province='广东省' and	city='中山市'	and town='黄圃镇'                then	'442014'
        |when province='广东省' and	city='中山市'	and town='东区街道'                 then	'442015'
        |when province='广东省' and	city='中山市'	and town='阜沙镇'                then	'442016'
        |when province='广东省' and	city='中山市'	and town='三角镇'                then	'442017'
        |when province='广东省' and	city='中山市'	and town='港口镇'                then	'442018'
        |when province='广东省' and	city='中山市'	and town='五桂山街道'                then	'442019'
        |when province='广东省' and	city='中山市'	and town='南区街道'                 then	'442020'
        |when province='广东省' and	city='中山市'	and town='板芙镇'                then	'442021'
        |when province='广东省' and	city='中山市'	and town='民众镇'                then	'442022'
        |when province='广东省' and	city='中山市'	and town='神湾镇'                then	'442023'
        |when province='广东省' and	city='中山市'	and town='南朗镇'                then	'442024'
        |
        |else regional_ID end region_id
        |
        |from table_v_get_matchID_ZSDG)
      """.stripMargin)
      .drop("regional_ID", "district")
      .withColumnRenamed("region_id", "regional_ID")
      .withColumnRenamed("districts", "district")

    resultData
  }


  /**
    * 匹配AedzId （含registration_institution 的）
    *
    * NEED : city,district,address,registration_institution   无字段先打上'-1'
    *
    * @param spark
    * @param dataFrame
    * @return
    */
  def match_AedzId(spark: SparkSession, dataFrame: DataFrame): DataFrame = {
    dataFrame.createOrReplaceTempView("datv")

    spark.sql(
      """
        |select *,
        |case
        |when city not in ('苏州市','湛江市','武汉市','锡林郭勒盟','威海市','宿迁市','烟台市','东营市')   then '-1'
        |
        |-- 苏州市:苏州工业园区
        |when city = '苏州市' and  (address  rlike '苏州工业园区' or registration_institution rlike '苏州工业园区') then '苏州工业园区'
        |
        |-- 湛江市:湛江经济技术开发区
        |when city = '湛江市' and district in ('赤坎区','霞山区','麻章区') then
        |	(	case when
        |		address like '%湛江经济技术开发区%' or address like '%湛江%经济%开发区%' or address like '%东海岛经济开发区%' or address like '%东海岛%' or
        |		address like '%东头山%' or address like '%民安%' or address like '%泉庄%' or address like '%东山%' or address like '%东简%' or address like '%乐华%' or
        |		address like '%硇洲%' or address like '%人民大道%' or address like '%乐山大道%' or address like '%龙潮路%' or address like '%海滨大道%' or
        |		address like '%观海长廊%' or address like '%龙平路%' or address like '%绿塘河%' or address like '%乐金路%' or address like '%开发区梧阔村%'
        |		then '湛江经济技术开发区'
        |		else district end
        |	)
        |
        |-- 武汉市：武汉东湖新技术开发区/武汉经济技术开发区
        |when city='武汉市' then
        |    (case
        |    when  district ='汉南区' then '武汉经济技术开发区'
        |   	when  registration_institution in ('武汉经济技术开发区（汉南区）行政审批局','武汉经济技术开发区（汉南区）市场监督管理局') then '武汉经济技术开发区'
        |   	when  registration_institution in ('武汉东湖新技术开发区市场监督管理局' ,'武汉市东湖生态旅游风景区食药监(工商 质监)局') then '武汉东湖新技术开发区'
        |   	when  address like '%东湖新技术开发区%' or address like '%东湖高新技术开发区%' then '武汉东湖新技术开发区'
        |   	when  district ='蔡甸区' and (address like '%沌阳%' or address like '%沌口%' or address like '%军山%')  then '武汉经济技术开发区'
        |    when  district in ('洪山区','江夏区') then
        |	    (case when address like '%东湖%' or address like '%南湖%' or address like '%关东%' or address like '%豹澥%' or address like '%九峰%' or
        |	    address like '%花山%' or address like '%左岭%' or address like '%滨湖%' or address like '%佛祖岭%' then '武汉东湖新技术开发区' else district end)
        |	when address not like '%汉阳%开发区%' and ( address like '%武汉经开区%' or address like '%武汉%经济技术开发区%') then '武汉经济技术开发区'
        |    else district end
        |	)
        |
        |-- 锡林郭勒盟 :乌拉盖管理区  152571  旗县级行政管理区
        |when city ='锡林郭勒盟' then
        |	(case
        |	when registration_institution like '%锡林郭勒盟工商行政管理局乌拉盖分局%' then '乌拉盖管理区'
        |	when district in ('东乌珠穆沁旗','锡林浩特市') and (address like '%乌拉盖管理区%' or address like '%乌拉盖巴音胡硕镇%' or address like '%巴音胡硕镇%') then '乌拉盖管理区'
        |	else district end
        |	)
        |
        |-- 威海市: 威海火炬高技术产业开发区 371071 /威海临港经济技术开发区 371073 /威海经济技术开发区 371072/威海南海新区 371078
        |when city ='威海市' then
        |	(case
        |	when registration_institution  in ('威海市火炬高技术产业开发区市场监督管理局','威海火炬高技术产业开发区市场监督管理局') then '威海火炬高技术产业开发区'
        |	when registration_institution  in ('威海市临港经济技术开发区市场监督管理局','威海临港经济技术开发区市场监督管理局') then '威海临港经济技术开发区'
        |	when registration_institution  in ('威海经济技术开发区市场监督管理局','威海市经济技术开发区市场监督管理局') then '威海经济技术开发区'
        |
        |	when district='环翠区' and (address like '%高技术产业开发区%' or address like '%火炬高技术产业%') then '威海火炬高技术产业开发区'
        |	when district='环翠区' and (address like '%临港经济技术%' or address like '%汪疃镇%' or address like '%蔄山镇%' or address like '%苘山镇%' or address like '%草庙子镇%' or address like '%黄岚办事处%') then '威海临港经济技术开发区'
        |	when district='环翠区' and (address like '%经济技术开发区%' or address like '%崮山镇%' or address like '%泊于镇%' or address like '%桥头镇%'
        |								or address like '%皇冠街道%' or address like '%凤林街道%' or address like '%西苑街道%') then '威海经济技术开发区'
        |	when district='文登区' and address like '%南海新区%' then '威海南海新区'
        |	else district end
        |	)
        |
        |--宿迁 uniqueid region_id
        |when city = '宿迁市' then
        |(
        |case
        |when address like '%郑楼镇%' or address like '%仓集镇%' or address like '%洋河镇%' or address like '%洋河新区%'
        |or registration_institution in ('宿迁洋河镇(市洋河新区)市场监督管理局')
        |then '洋河新区'
        |when address like '晓店镇' or address like '%井头乡%' or address like '%黄墩镇%' or address like '%黄墩镇%'
        |or address like '%祥和社区%' or address like '%湖滨新区%' then '湖滨新区'
        |when address like '双庄镇' and (address like '靳塘' or address like '白堡' or address like '双庄' or address like '牌坊' or address like '魏井')--双庄街道
        |or address like '蔡集镇' or address like '%宿城经济开发区%' or
        |address like '%苏州宿迁工业园区%'
        |or registration_institution in ('苏州宿迁工业园区市场监督管理局')
        |then '苏州宿迁工业园区'
        |when address like '%朱庄%' or address like '%叶圩%' or address like '%前周%' or address like '%许圩%' or address like '%杨楼%' or address like '%南蔡乡%'
        |or address like '%宿迁经济开发区%'
        |or registration_institution in ('宿迁经济技术开发区市场监督管理局','宿迁经济技术开发区行政审批局')
        |then '宿迁经济开发区'
        |else district end
        |)
        |--烟台
        |when city = '烟台市' then
        |(
        |case
        |when city='烟台市' and district = '福山区' and
        |(registration_institution='烟台经济技术开发区市场监督管理局' or address like '%福莱山%' or address like '%古现%' or address like '%大季家%' or address like '%八角%'
        |or (address like'%开发区%' and registration_institution not in('拉萨经济技术开发区市场监督管理局','烟台市莱山区市场监督管理局')))
        |then '烟台经济技术开发区'
        |else district end
        |)
        |--东营市
        |when city = '东营市' then
        |(
        |case when district = '东营区' and
        |(registration_institution='东营经济技术开发区市场监督管理局' or
        | address like '%东城街道%' or  address like '%胜利街道%'
        | or address like '%胜利大街%'
        | or (address like '%南一路%' and address like '%228号%')
        | or address like '%东四路%'
        | or address like '%泉州路%'
        | or address like '%沂州路%'
        | or address like '%府前大街%'
        | or address like '%府前街%'
        | or address like '%沂河路%'
        | or address like '%东六路%'
        | or address like '%东七路%'
        | or address like '%辽河路%'
        | or (address like '%东三路%' and address like '%171号%')
        | or (address like '%东三路%' and address like '%138号%')
        | or (address like '%黄河%' and address like '%38号%')
        | or (address like '%北一路%' and address like '%132号%')
        | or address like '%开发区%'
        | ) then '东营经济技术开发区' else district end
        |)
        |else  district end aedzId
        | from datv
      """.stripMargin)
  }


}

